const wrapFields = new Map();

wrapFields.set('mssql', '[$1]');
wrapFields.set('mysql', '`$1`');
wrapFields.set('sqlite', '`$1`');

/**
 * 数据库查询脚本生成器。
 */
class SqlBuilder {
    /**
     * 构造函数。
     */
    constructor(executeObject, dbtype = 'mysql', hasOrderBy = false) {
        this.sorts = [];
        this.segments = [];
        this._hasWhere = false;
        this._hasOrderBy = hasOrderBy;

        this.dbtype = dbtype;
        this.skipsNumber = 1;
        this.takesNumber = 10;
        this.enablePaged = false;
        this._totalSql = '';

        if (executeObject) {
            this._totalSql = executeObject.$totalSql$ || '';

            let index = 0;
            let values = {};
            let filters = [];
            let objNames = Object.getOwnPropertyNames(executeObject);

            objNames.forEach(item => {
                if (item != 'filters' && item != 'sorts') {
                    values[item] = executeObject[item];
                }
            });

            this.params = values;
            this.paramNames = Object.getOwnPropertyNames(values);

            // 分页处理
            if (executeObject.pageIndex) {
                this.paging();
            }

            if (executeObject.filters && Array.isArray(executeObject.filters)) {
                executeObject.filters.forEach(f => {
                    if (f.field) {
                        let pName = `p${index}`;

                        f.op = f.op || '=';
                        f.join = f.join || 'and';
                        f.queryable = !!f.value;
                        f.paramName = '@' + pName;

                        values[pName] = f.value;

                        if (f.field.indexOf('.') == -1 && wrapFields.get(dbtype)) {
                            f.field = wrapFields.get(dbtype).replace('$1', f.field);
                        }

                        index++;
                        filters.push(f);
                    }
                });
            }

            // 排序处理
            (executeObject.sorts || []).forEach(s => {
                s.by = s.by || 'asc';

                if (s.field) {
                    s.by.toLowerCase() == 'asc' ? this.asc(s.field) : this.desc(s.field);
                }
            });

            // 查询条件
            filters.forEach(f => {
                let segment = '';

                switch (f.op.toLowerCase()) {
                    case '=':
                    case '>':
                    case '>=':
                    case '<':
                    case '<=':
                        if (this.dbtype == 'mysql') {
                            segment = `${f.field}${f.op}${f.paramName}`;
                        }

                        if (this.dbtype == 'sqlite') {
                            segment = `${f.field}${f.op}${f.paramName}`;
                        }

                        if (this.dbtype == 'mssql') {
                            segment = `${f.field}${f.op}${f.paramName}`;
                        }

                        break;
                    case 'like':
                        if (this.dbtype == 'mysql') {
                            segment = `${f.field} LIKE CONCAT('%', ${f.paramName}, '%')`;
                        }

                        if (this.dbtype == 'sqlite') {
                            segment = `${f.field} LIKE '%' || ${f.paramName} || '%'`;
                        }

                        if (this.dbtype == 'mssql') {
                            segment = `${f.field} LIKE '%'+` + f.paramName + `+'%'`;
                        }

                        break;
                    case 'starts':
                        if (this.dbtype == 'mysql') {
                            segment = `${f.field} LIKE CONCAT(${f.paramName}, '%')`;
                        }

                        if (this.dbtype == 'sqlite') {
                            segment = `${f.field} LIKE ${f.paramName} || '%'`;
                        }

                        if (this.dbtype == 'mssql') {
                            segment = `${f.field} LIKE ` + f.paramName + `+'%'`;
                        }

                        break;
                    case 'ends':
                        if (this.dbtype == 'mysql') {
                            segment = `${f.field} LIKE CONCAT('%', ${f.paramName})`;
                        }

                        if (this.dbtype == 'sqlite') {
                            segment = `${f.field} LIKE '%' || ${f.paramName}`;
                        }

                        if (this.dbtype == 'mssql') {
                            segment = `${f.field} LIKE '%'+` + f.paramName;
                        }

                        break;
                }

                if (f.join.toLowerCase() == 'or') {
                    this.or(segment, f.queryable);
                } else {
                    this.and(segment, f.queryable)
                }
            });
        }
    }

    /**
     * 是否已经包含where
     */
    hasWhere () {
        this._hasWhere = true;

        return this;
    }

    /**
     * 追加and查询条件
     * @param {String} segment 查询片段
     * @param {Object} value 值
     * @param {Object | Function} queryable 参与查询的条件(值或者有返回值的函数)
     */
    and (segment, queryable) {
        if (!queryable) {
            let properties = this._getProperties(segment);

            queryable = properties.length > 0 && properties.every(element => !!element);
        }

        this.segments.push({
            segment,
            queryable,
            join: 'and'
        });

        return this;
    }

    /**
     * 追加or查询条件
     * @param {String} segment 查询片段
     * @param {Object} value 值
     * @param {Object | Function} queryable 参与查询的条件(值或者有返回值的函数)
     */
    or (segment, queryable) {
        if (!queryable) {
            let properties = this._getProperties(segment);

            queryable = properties.length > 0 && properties.every(element => !!element);
        }

        this.segments.push({
            segment,
            queryable,
            join: 'or'
        });

        return this;
    }

    /**
     * 追加升序排序
     * @param {String} column 排序字段
     */
    asc (column) {
        this.sorts.push({
            column,
            by: 'asc'
        });

        return this;
    }

    /**
     * 追加倒序排序
     * @param {String} column 排序字段
     */
    desc (column) {
        this.sorts.push({
            column,
            by: 'desc'
        });

        return this;
    }

    /**
     * 启用分页
     */
    paging () {
        let pgIndex = parseInt(this._getParameter("PageIndex")) || 1;
        let pgSize = parseInt(this._getParameter("PageSize")) || 10;

        this.enablePaged = true;
        this.takesNumber = pgSize < 1 ? 10 : pgSize;
        this.skipsNumber = ((pgIndex < 1 ? 1 : pgIndex) - 1) * this.takesNumber;

        return this;
    }

    /**
     * 生成sql脚本
     */
    build () {
        let sqlSegment = '';

        for (let index = 0; index < this.segments.length; index++) {
            let element = this.segments[index];
            let queryable = typeof element.queryable == 'function' ? element.queryable.call(this.params, this.params) : element.queryable;

            if (queryable) {
                if (sqlSegment == '') {
                    sqlSegment += this._hasWhere ? element.join : '';
                } else {
                    sqlSegment += element.join;
                }

                sqlSegment += ` ${element.segment} `;
            }
        }

        if (sqlSegment != '') {
            sqlSegment = this._hasWhere ? '' : ' where' + sqlSegment;
        }

        let sorted = false;

        if (this.sorts.length > 0) {
            sqlSegment += ' ORDER BY ';
            let orders = [];

            this.sorts.forEach(element => {
                orders.push(` ${element.column} ${element.by}`);
            });

            sorted = true;
            sqlSegment += orders.join(',');
        }

        if (this.enablePaged && (this.dbtype == 'mysql' || this.dbtype == 'sqlite')) {
            sqlSegment += ` LIMIT ${this.skipsNumber}, ${this.takesNumber}`;
        } else if (this.enablePaged && this.dbtype == 'mssql') {
            if (sqlSegment.toLowerCase().indexOf(' order by ') == -1 && !this._hasOrderBy) {
                sqlSegment += ' order by 1 desc';
            }

            sqlSegment += ` offset ${this.skipsNumber} rows fetch next ${this.takesNumber} rows only`;
        }

        return sqlSegment;
    }

    getValues () {
        return this.params || {};
    }

    getTotalSql (fullSql = '') {
        if (this._totalSql && this._totalSql.trim().length > 0) {
            return this._totalSql;
        }

        fullSql = fullSql || '';

        if (fullSql.length > 0 && this.enablePaged) {
            let temp = fullSql.toLowerCase();
            let lastIndex = temp.indexOf('order ');

            if ((this.dbtype == 'mysql' || this.dbtype == 'sqlite') && temp.indexOf('limit') != -1) {
                fullSql = fullSql.substring(0, temp.indexOf('limit'));
            }

            if (temp.includes('group')) {
                this._totalSql = `SELECT COUNT(*) as totals FROM ( SELECT COUNT(*) n ${fullSql.substring(temp.indexOf('from '), lastIndex == -1 ? fullSql.length : lastIndex)} ) t`;
            } else {
                this._totalSql = `SELECT COUNT(*) as totals ${fullSql.substring(temp.indexOf('from '), lastIndex == -1 ? fullSql.length : lastIndex)}`;
            }
        } else {
            return 'SELECT FOUND_ROWS() AS totals;';
        }

        return this._totalSql;
    }

    /**
     * 获取参数值
     * @param {String} propName
     */
    _getParameter (propName) {
        let prop = this.paramNames.find(p => p.toLowerCase() == propName.toLowerCase());

        if (prop) {
            return this.params[prop];
        }

        return null;
    }

    /**
     * 获取查询片段中含有的属性值。
     * @param {String} segment
     * @returns 属性值列表
     */
    _getProperties (segment) {
        this.properties = this.properties || [];

        if (this.properties.length > 0) {
            return this.properties;
        }

        let matches = segment.match(/\@(\w+)/ig);

        if (matches) {
            matches.forEach(element => {
                let propName = this.paramNames.find(p => p.toLowerCase() == element.substring(1).toLowerCase());

                if (propName) {
                    this.properties.push(this.params[propName]);
                }
            });
        }

        return this.properties;
    }
}

module.exports = SqlBuilder;